package co.edu.ucatolica.sislab.persistence.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import co.edu.ucatolica.sislab.persistence.DatabaseException;
import co.edu.ucatolica.sislab.persistence.DatabasePersistorHelper;
import co.edu.ucatolica.sislab.persistence.dto.DomainListItemDTO;
import co.edu.ucatolica.sislab.persistence.utils.DatabaseOperations;
import co.edu.ucatolica.sislab.persistence.utils.PersistenceUtils;
import co.edu.ucatolica.sislab.persistence.utils.SQLSISLABDBConstants;
import co.edu.ucatolica.sislab.utils.DatabaseTables;

public class DomainListPersistorHelper {
	/** Permite registrar mensajes del sistema.*/
	private static Logger logger = LoggerFactory.getLogger(DomainListPersistorHelper.class);
	public static Long getDomainListIdByName(Long transactionNumber, String domainListName) throws DatabaseException {
		Connection dbConnection = null;
		PreparedStatement ps = null;
		try {
			dbConnection = DatabasePersistorHelper.getConexion(transactionNumber);
			String sql = DatabasePersistorHelper.getSql(SQLSISLABDBConstants.SQL_SELECT_DOMAIN_LIST_ID_BY_NAME);
			
			if ((dbConnection != null) && (sql != null)) {
				ps = dbConnection.prepareStatement(sql);
				ps.setString(1, domainListName);
				ResultSet rs = ps.executeQuery();
				if(rs.next()){
					return rs.getLong("id");
				}
			}
		} catch (SQLException e) {
			logger.error("No fue posible ejecutar la consulta de id de lista de valores: " + domainListName + " - " + e.getMessage());
			throw new DatabaseException(DatabaseTables.LIST_VALUE, DatabaseOperations.SELECT, e.toString());
		} catch (DatabaseException e) {
			logger.error("No fue posible ejecutar la consulta de id de lista de valores: " + domainListName + " - " + e.getMessage());
			throw new DatabaseException(DatabaseTables.LIST_VALUE, DatabaseOperations.SELECT, e.toString());
		} finally {
			PersistenceUtils.closeConnectionIssues(ps);
		}
		return null;
	}
	public static List<DomainListItemDTO> getDomainListItemsByIdDomainList(Long transactionNumber, Long idDomainList) throws DatabaseException {
		Connection dbConnection = null;
		PreparedStatement ps = null;
		try {
			dbConnection = DatabasePersistorHelper.getConexion(transactionNumber);
			String sql = DatabasePersistorHelper.getSql(SQLSISLABDBConstants.SQL_SELECT_DOMAIN_LIST_ITEMS_BY_DOMAIN_LIST_ID);
			//SELECT * FROM `list_value_item` WHERE list_value_id = 1
			if ((dbConnection != null) && (sql != null)) {
				ps = dbConnection.prepareStatement(sql);
				ps.setLong(1, idDomainList);
				ResultSet rs = ps.executeQuery();
				List<DomainListItemDTO> domainListItemDTOs = new ArrayList<DomainListItemDTO>();
				while(rs.next()){
					DomainListItemDTO domainListItemDTO = new DomainListItemDTO();
					domainListItemDTO.setId(rs.getLong("id"));
					domainListItemDTO.setNombre(rs.getString("nombre"));
					domainListItemDTOs.add(domainListItemDTO);
				}
				return domainListItemDTOs;
			}
		} catch (SQLException e) {
			logger.error("No fue posible ejecutar la consulta de iteam de lista de valores con id: " + idDomainList + " - " + e.getMessage());
			throw new DatabaseException(DatabaseTables.LIST_VALUE_ITEM, DatabaseOperations.SELECT, e.toString());
		} catch (DatabaseException e) {
			logger.error("No fue posible ejecutar la consulta de iteam de lista de valores con id: " + idDomainList + " - " + e.getMessage());
			throw new DatabaseException(DatabaseTables.LIST_VALUE_ITEM, DatabaseOperations.SELECT, e.toString());
		} finally {
			PersistenceUtils.closeConnectionIssues(ps);
		}
		return null;
	}
}
